String Functions

Table of Contents

1. Manipulations

   • CONCAT
   • LOWER & UPPER
   • TRIM
   • REPLACE

2. Calculation

   • LEN

3. Substring Extraction

   • LEFT & RIGHT
   • SUBSTRING

4. Nesting Functions

1. Manipulations

1.1 CONCAT

Task 1 – Concatenate columns Write a query that concatenates first_name and country from the customers table into a single column called full_info, separated by a hyphen (-).

SELECT 
    CONCAT(first_name, '-', country) AS full_info
FROM customers

1.2 LOWER & UPPER

Task 2 – Convert first names to lowercase Write a query that returns all customers’ first names in lowercase, aliased as lower_case_name.

SELECT 
    LOWER(first_name) AS lower_case_name
FROM customers

Task 3 – Convert first names to uppercase Write a query that returns all customers’ first names in uppercase, aliased as upper_case_name.

SELECT 
    UPPER(first_name) AS upper_case_name
FROM customers

1.3 TRIM

Task 4 – Find names with leading/trailing spaces Write a query that finds customers whose first_name has leading or trailing spaces. Show:

• first_name
• len_name = length of first_name
• len_trim_name = length of TRIM(first_name)
• flag = difference between original length and trimmed length

Filter only rows where the original and trimmed lengths are different.

SELECT 
    first_name,
    LEN(first_name) len_name,
    LEN(TRIM(first_name)) len_trim_name,
    LEN(first_name) - LEN(TRIM(first_name)) flag
FROM customers
WHERE LEN(first_name) != LEN(TRIM(first_name))
-- WHERE first_name != TRIM(first_name)

1.4 REPLACE

Task 5 – Replace dashes in a phone number Write a query that takes the literal string '123-456-7890' as phone and returns a clean_phone version where all dashes (-) are replaced with slashes (/).

SELECT
    '123-456-7890' AS phone,
    REPLACE('123-456-7890', '-', '/') AS clean_phone

Task 6 – Change file extension from .txt to .csv Write a query that takes the literal string 'report.txt' as old_filename and returns a new_filename where the .txt extension is replaced by .csv.

SELECT
    'report.txt' AS old_filename,
    REPLACE('report.txt', '.txt', '.csv') AS new_filename

2. Calculation

2.1 LEN

Task 7 – Calculate name length Write a query that returns each customer’s first_name and its length as name_length.

SELECT 
    first_name, 
    LEN(first_name) AS name_length
FROM customers

3. Substring Extraction

3.1 LEFT & RIGHT

Task 8 – First two characters of first name Write a query that returns first_name and the first two characters of the trimmed first_name, aliased as first_2_chars.

SELECT 
    first_name,
    LEFT(TRIM(first_name), 2) AS first_2_chars
FROM customers

Task 9 – Last two characters of first name Write a query that returns first_name and the last two characters of first_name, aliased as last_2_chars.

SELECT 
    first_name,
    RIGHT(first_name, 2) AS last_2_chars
FROM customers

3.2 SUBSTRING

Task 10 – Remove first character from trimmed name Write a query that returns first_name and a trimmed_name where you:

• trim spaces from first_name
• remove the first character
• keep the rest of the characters using SUBSTRING

SELECT 
    first_name,
    SUBSTRING(TRIM(first_name), 2, LEN(first_name)) AS trimmed_name
FROM customers

4. Nesting Functions

4.1 UPPER(LOWER(first_name))

Task 11 – Demonstrate function nesting Write a query that selects first_name and a second column nesting, where nesting applies LOWER and then UPPER to first_name using nested functions.

SELECT
    first_name, 
    UPPER(LOWER(first_name)) AS nesting
FROM customers